// sous module specfique de gestion des acces a la table LOCATION
// Table : Location(idlogt, idcli, duree)
package bdd;

import java.sql.*;
import java.util.*;
import beans.*;

public class DB_LOCATION {
  Connection conn;
  PreparedStatement ps_select;
  PreparedStatement ps_insert;
  PreparedStatement ps_update;
  PreparedStatement ps_delete;

  public DB_LOCATION(Connection conn) {
     this.conn=conn;
     try{
        ps_select = conn.prepareStatement("select duree from Location where idlogt=? and idcli=?");
  	ps_insert = conn.prepareStatement("insert into Location values(?,?,?)");
        ps_update = conn.prepareStatement("update Location set duree=? where idlogt=? and idcli=?");
        ps_delete = conn.prepareStatement("delete from Location where idlogt=? and idcli=?"); 
     } catch(SQLException ex){System.out.println(ex);}
  }
      
  public Location getLocation(int idlogt, int idcli) {
     Location p=null;
     try{
      ps_select.setInt(1,idlogt);
      ps_select.setInt(2,idcli);
      ResultSet rs = ps_select.executeQuery();
      if(rs.next()){
	  int duree = rs.getInt("duree");
          p = new Location(idlogt,idcli,duree);        
	    }
     } catch(SQLException ex){ System.out.println(ex);}
     return p;
  }

  public void insertLocation(Location p){
		try{
			ps_insert.setInt(1,p.getIdlogt());
			ps_insert.setInt(2,p.getIdcli());
			ps_insert.setInt(3,p.getDuree());
			ps_insert.executeUpdate();
     		} catch(SQLException ex){System.out.println(ex);}  
  }
        
  public void updateLocation(Location p){
		try{ 
			ps_update.setInt(1,p.getDuree());
			ps_update.setInt(2,p.getIdlogt());
			ps_update.setInt(3,p.getIdcli());
			ps_update.executeUpdate();
		} catch(SQLException ex){System.out.println(ex);}     
  }

  public void deleteLocation(int idlogt, int idcli){
        try{
		ps_delete.setInt(1,idlogt);
		ps_delete.setInt(2,idcli);
		ps_delete.executeUpdate();
     	      } catch(SQLException ex){System.out.println(ex);} 
  }

  public ArrayList<Location> getLocations(String req) {
	// pre-condition: req est de la forme "SELECT * FROM Location ..."
	// car il s'agit d'extraire un ensemble de Locations

        Location p;
	ArrayList<Location> alp = null;

	try{ 
		alp = new ArrayList<Location>(); 
		Statement st = conn.createStatement(); 
		ResultSet rs = st.executeQuery(req); 
		while(rs.next()){ 
			p = new Location(rs.getInt("idlogt"), rs.getInt("idcli"), rs.getInt("duree")); 
			alp.add(p); 
		} 
	} catch(SQLException ex){System.out.println(ex);} 
	return alp;
  }

  public ArrayList<Location> getLocations() {
	return getLocations("select * from Location");
  }


}
